package DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import Model.Funcao;
import Utils.Conexao;

import com.mysql.jdbc.PreparedStatement;

public class FuncaoDAO {
	public static void insert(Funcao funcao) throws SQLException
	{
		Connection con = Conexao.getConection();		
		String sqlQuery = "INSERT INTO tb_funcao(descricao) "+
			 	" VALUES (?)";
		
		PreparedStatement prep = FuncaoDAO.setObjectPraparedStatement(funcao, sqlQuery, con);
		prep.executeUpdate();
		con.close();		
		prep.close();
	}
	
	public static void update(Funcao funcao) throws SQLException
	{
		Connection con = Conexao.getConection();		
		String sqlQuery = "UPDATE tb_funcao SET" +
				",descricao = ? " +
				" WHERE id = ? ";
		
		PreparedStatement prep = FuncaoDAO.setObjectPraparedStatement(funcao, sqlQuery, con) ;
		prep.executeUpdate();
		con.close();
		prep.close();		
	}	
	
	public static void Delete(int id) throws SQLException
	{
		Connection con = Conexao.getConection();		
		String sqlQuery = "delete from tb_funcao where id = ? ";
		PreparedStatement prep = (PreparedStatement) con.prepareCall(sqlQuery);
		prep.setInt(1, id);		
		prep.executeUpdate();
		
		con.close();
		prep.close();
	}
	
	public static Funcao LoadObject(int id_tipo_capilar) throws SQLException
	{
		Funcao funcao = null;
		Connection con = Conexao.getConection();
		
		String sqlQuery = "SELECT " +
						"id " +
						",descricao " +
						"FROM tb_funcao " +
						"WHERE id = ? ";
		
		PreparedStatement prep = (PreparedStatement) con.prepareCall(sqlQuery);
		prep.setInt(1, id_tipo_capilar);
		ResultSet res = prep.executeQuery();
		
		if(res.next())
			funcao = FuncaoDAO.setInstance(res);		
		
		return funcao;	
	}
	
	public static ArrayList<Funcao> getFuncaoArrayList()
	{
		ArrayList<Funcao> arrFuncao = new ArrayList<Funcao>(); 
		Connection con  = Conexao.getConection();
		try
		{
			String sqlQuery = "SELECT " +
					"id " +
					",descricao " +
					"FROM tb_funcao";
			
			PreparedStatement prep = (PreparedStatement) con.prepareCall(sqlQuery);
			ResultSet rs = prep.executeQuery();
			
			while(rs.next())
			{
				Funcao funcao = FuncaoDAO.setInstance(rs);
				arrFuncao.add(funcao);
			}					
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return arrFuncao;
	}
	
	public static Funcao setInstance(ResultSet res) throws SQLException
	{
		Funcao funcao = new Funcao();
		funcao.setId(res.getInt("id"));
		funcao.setDescricao(funcao.getDescricao());
		return funcao;		
	}
	
	private static PreparedStatement setObjectPraparedStatement(Funcao funcao, String sqlQuery, Connection con ) throws SQLException
	{		
		PreparedStatement prep = (PreparedStatement) con.prepareCall(sqlQuery);
		prep.setString(1, funcao.getDescricao());
		
		if(funcao.getId() > 0)
			prep.setInt(2, funcao.getId());
	
		return prep; 		
	}
}
